# 接收邮件并下载邮件附件
import poplib
import email
import datetime
import time
from email.parser import Parser
from email.header import decode_header
import traceback
import telnetlib
import sys


# from email.utils import parseaddr


class c_step4_get_email:
    # 字符编码转换
    @staticmethod
    def decode_str(str_in):
        value, charset = decode_header(str_in)[0]
        if charset:
            value = value.decode(charset)
        return value

    # 解析邮件,获取附件
    @staticmethod
    def get_att(msg_in, str_day_in):
        # import email
        attachment_files = []
        for part in msg_in.walk():
            # 获取附件名称类型
            file_name = part.get_filename()
            # contType = part.get_content_type()
            if file_name:
                h = email.header.Header(file_name)
                # 对附件名称进行解码
                dh = email.header.decode_header(h)
                filename = dh[0][0]
                if dh[0][1]:
                    # 将附件名称可读化
                    filename = c_step4_get_email.decode_str(str(filename, dh[0][1]))
                    print(filename)
                    # filename = filename.encode("utf-8")
                # 下载附件
                data = part.get_payload(decode=True)
                # 在指定目录下创建文件，注意二进制文件需要用wb模式打开
                att_file = open(r'C:\Users\Administrator\Desktop\test' + '\\' + filename, 'wb')
                attachment_files.append(filename)
                att_file.write(data)  # 保存附件
                att_file.close()
        return attachment_files

    @staticmethod
    def run_ing():
        # 输入邮件地址, 口令和POP3服务器地址:
        email_user = 'zgcindex_zh@163.com'
        # 此处密码是授权码,用于登录第三方邮件客户端
        password = input('请输入您的密码：')  # 
        pop3_server = 'pop.163.com'
        # 日期赋值
        day = datetime.date.today() - datetime.timedelta(days=1)#周一days=3
        str_day = str(day).replace('-', '')
        print(str_day)
        # 连接到POP3服务器,有些邮箱服务器需要ssl加密，可以使用poplib.POP3_SSL
        try:
            telnetlib.Telnet('pop.163.com', 995)
            server = poplib.POP3_SSL(pop3_server, 995, timeout=10)
        except:
            time.sleep(5)
            server = poplib.POP3(pop3_server, 110, timeout=10)
        # server = poplib.POP3(pop3_server, 110, timeout=120)
        # 可以打开或关闭调试信息
        # server.set_debuglevel(1)
        # 打印POP3服务器的欢迎文字:
        print(server.getwelcome().decode('gb18030'))#utf-8
        # 身份认证:
        server.user(email_user)
        server.pass_(password)
        # 返回邮件数量和占用空间:
        print('Messages: %s. Size: %s' % server.stat())
        # list()返回所有邮件的编号:
        resp, mails, octets = server.list()
        # 可以查看返回的列表类似[b'1 82923', b'2 2184', ...]
        print(mails)
        index = len(mails)
        # 倒序遍历邮件
        # for i in range(index, 0, -1):
        # 顺序遍历邮件
        for i in range(index, index - 100, -1):  # 1988, 1989 0,index+1,-1    index，index+1
            resp, lines, octets = server.retr(i)
            # lines存储了邮件的原始文本的每一行,
            # 邮件的原始文本:
            msg_content = b'\r\n'.join(lines).decode('gb18030')#('utf-8')
            # 解析邮件:
            msg = Parser().parsestr(msg_content)
            # 获取邮件时间,格式化收件时间
            date1 = time.strptime(msg.get("Date")[0:24], '%a, %d %b %Y %H:%M:%S')
            # 邮件时间格式转换
            date2 = time.strftime("%Y%m%d", date1)
            if date2 < str_day:
                # 倒叙用break
                break
                # 顺叙用continue
                # continue
            else:
                # 获取附件
                c_step4_get_email.get_att(msg, str_day)

        # print_info(msg)
        server.quit()


if __name__ == '__main__':
    # @version : 3.4
    # @Author  : robot_lei
    # @Software: PyCharm Community Edition
    # log_path = 'C:\\fakepath\\log.log'
    # logging.basicConfig(filename=log_path)
    origin = sys.stdout
    f = open(r'C:\Users\Administrator\Desktop\receive\123.txt', 'w')
    sys.stdout = f
    try:
        c_step4_get_email.run_ing()
    except Exception as e:
        s = traceback.format_exc()
        print(e)
        tra = traceback.print_exc()
    sys.stdout = origin
    f.close()

    #将下好的表合并在一起
import pandas as pd
import os
file=r'C:\Users\Administrator\Desktop\test'
total_table=pd.DataFrame(columns=['总后大类','总后子类','品牌','产品名称','关键参数','电商价格/合同平均价','电商链接','其他渠道证明','审核意见','发件人邮箱'])
for parents,dirnames,filenames in os.walk(file):
    for filename in filenames:
        df=pd.read_excel(os.path.join(parents,filename))
        df1=df.drop(index=0)
        total_table=total_table.append(df1,ignore_index=True)
total_table.to_excel(r'E:\star1\333.xlsx')


#将合并的表中url列一一审核并填入审核意见
import re
import requests
import pandas as pd
import numpy as np
from lxml import etree
from selenium.webdriver import Firefox
from selenium.webdriver.firefox.options import Options
from selenium.webdriver.support.wait import WebDriverWait

fileName = r'E:\star1\333.xlsx'
df = pd.read_excel(fileName)
link = df['电商链接']

result = []
for i in range(len(link)):
    main_url = link[i]
    if type(main_url) == float :
        print("其他销售渠道证明")
        result.append("其他销售渠道证明")
    else:
        if "jd" in str(main_url):
            options = Options()
            options.add_argument('-headless')  # 无头X参数
            driver = Firefox(firefox_options=options,executable_path = r"C:\Users\Administrator\Desktop\geckodriver.exe")  # 配了环境变量第一个参数就可以省了，不然传绝对路径
            wait = WebDriverWait(driver, timeout=3)
            driver.get(main_url)
            source = driver.page_source 
            driver.quit()
            html = etree.HTML(source)
            ziying = html.xpath(
                "//div[@class='name goodshop EDropdown']/em/text()")
            if "自营" in str(ziying):
                    name = html.xpath(
                            "//div[@class='sku-name']/text()")
                    if ("定制"in str(name))or ("防弹"in str(name))or ("射击"in str(name))or ("订制"in str(name))or ("帐篷"in str(name))or ("卫星"in str(name))or ("靶"in str(name)):
                        print("定制/专用类产品暂不通过")
                        result.append("定制/专用类产品暂不通过")
                    else:
                        wuhuo = html.xpath(
                            "//div[@id='store-prompt']/strong/text()")
                        if "无货" in str(wuhuo):
                            print("无货，请按要求提供在销渠道证明")
                            result.append("无货，请按要求提供在销渠道证明")
                        else:
                            print("通过")
                            result.append("通过")
            else:
                print("非自营，请按要求提供在销渠道证明")
                result.append("非自营，请按要求提供在销渠道证明")
                
        elif "gome" in str(main_url):
            options = Options()
            options.add_argument('-headless')  # 无头X参数
            driver = Firefox(firefox_options=options,executable_path = r"C:\Users\Administrator\Desktop\geckodriver.exe")  # 配了环境变量第一个参数就可以省了，不然传绝对路径
            wait = WebDriverWait(driver, timeout=3)
            driver.get(main_url)
            source = driver.page_source 
            driver.quit()
            html = etree.HTML(source)
            ziying = html.xpath(
                "//span[@class='identify']/text()")
            if len(ziying) == 1:
                    name = html.xpath(
                            "//div[@class='hgroup']/text()")
                    if ("定制"in str(name))or ("防弹"in str(name))or ("射击"in str(name))or ("订制"in str(name))or ("帐篷"in str(name))or ("卫星"in str(name))or ("靶"in str(name)):
                        print("定制/专用类产品暂不通过")
                        result.append("定制/专用类产品暂不通过")
                    else:
                        wuhuo = html.xpath(
                            "//div[@id='store-prompt']/strong/text()")
                        if "无货" in str(wuhuo):
                            print("无货，请按要求提供在销渠道证明")
                            result.append("无货，请按要求提供在销渠道证明")
                        else:
                            print("通过")
                            result.append("通过")
            else:
                 print("非自营，请按要求提供在销渠道证明")
                 result.append("非自营，请按要求提供在销渠道证明")
        elif "suning" in str(main_url):
            options = Options()
            options.add_argument('-headless')  # 无头X参数
            driver = Firefox(firefox_options=options,executable_path = r"C:\Users\Administrator\Desktop\geckodriver.exe")  # 配了环境变量第一个参数就可以省了，不然传绝对路径
            wait = WebDriverWait(driver, timeout=3)
            driver.get(main_url)
            source = driver.page_source 
            driver.quit()
            html = etree.HTML(source)
            ziying = html.xpath(
                "//h1[@id='itemDisplayName']//span//text()")
            if "自营" in str(ziying):
                    name = html.xpath(
                              "//h1[@id='itemDisplayName']/text()")
                    if ("定制"in str(name))or ("防弹"in str(name))or ("射击"in str(name))or ("订制"in str(name))or ("帐篷"in str(name))or ("卫星"in str(name))or ("靶"in str(name)):
                        print("定制/专用类产品暂不通过")
                        result.append("定制/专用类产品暂不通过")
                    else:
                        wuhuo = html.xpath(
                            "//div[@id='store-prompt']/strong/text()")
                        if "无货" in str(wuhuo):
                            print("无货，请按要求提供在销渠道证明")
                            result.append("无货，请按要求提供在销渠道证明")
                        else:
                            print("通过")
                            result.append("通过")
            else:
                print("非自营，请按要求提供在销渠道证明")
                result.append("非自营，请按要求提供在销渠道证明")
        else:
                 print("非自营，请按要求提供在销渠道证明")
                 result.append("非自营，请按要求提供在销渠道证明")
         
df['审核意见'] = result
df.to_excel(fileName)

#按邮箱对应回复反馈邮件
from email.mime.text import MIMEText
import smtplib
from email.header import Header
from email.utils import parseaddr,formataddr#设置编码格式
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email import encoders
import openpyxl
import pandas as pd
import time
time.strftime("%Y/%m/%d", time.localtime())

data_path = r"C:\Users\Administrator\Desktop\0807zongbiao.xlsx"
data = pd.read_excel(data_path)

reply_root_path = r"C:\Users\Administrator\Desktop\ttt"

# for email_id,value in data.groupby("发件人邮箱"):
#     current_time = time.strftime("%Y%m%d", time.localtime())
#     reply_path = reply_root_path + "\回复附件#" +  current_time+ "#" + email_id + '.xlsx'
#     value.reset_index(drop=True).to_excel(reply_path)

#打开excel文件,读取邮箱地址和审核意见列
# wb=openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\0801zongbiao.xlsx')
# sheet=wb.get_active_sheet()
# to_addrs=list(sheet.columns)[9]
# body=list(sheet.columns)[9]
#for item in list(sheet.columns)[10]:
    #print(item.value)

#将用户名编码设置成UTF-8
def _format_addr(s):
    name,addr=parseaddr(s)
    return formataddr((Header(name, 'utf-8').encode(),addr))


#定义email的地址，口令和SMTP服务器地址
from_addr='zgcindex_zh@163.com'
password=input('请输入发送邮箱的密码：')#
smtp_server='smtp.163.com'
# to_addr='sx_wy123@163.com'



#定义邮件本身内容
# msg=MIMEMultipart()
# msg['From']=_format_addr('发送者的ReedSun<%s>'%from_addr)
# msg['To']=_format_addr('接收者的ReedSun<%s>'%to_addr)
# msg['Subject']=Header('回复','utf-8').encode()

#定义邮件正文
# msg.attach(MIMEText('使用python发来的邮件3','plain','utf-8'))

#加附件
for email_id,value in data.groupby("发件人邮箱"):
    current_time = time.strftime("%Y%m%d", time.localtime())
    reply_path = reply_root_path + "\回复附件#" +  current_time+ "#" + email_id + '.xlsx'#文件名
    value.reset_index(drop=True).to_excel(reply_path)
    to_addr=email_id
    msg=MIMEMultipart()
    msg['From']=_format_addr('发送者的ReedSun<%s>'%from_addr)
    msg['To']=_format_addr('接收者的ReedSun<%s>'%to_addr)
    msg['Subject']=Header('总后审核意见','utf-8').encode()
    msg.attach(MIMEText('总后新品申请审核意见，详情请见附件！注：有多封申请邮件的公司，不再分别回复，统一一并回复到附件中！请大家一定按要求格式申请，否则可能会无法正常申请上架，谢谢配合~','plain','utf-8'))
    
    with open(reply_path,'rb') as f:
         # 设置附件的MIME和文件名，这里是jpg类型,可以换png或其他类型:
        mime=MIMEBase("\回复附件#" + current_time+ "#" + email_id, 'xlsx', filename="\回复附件#" +  current_time+ "#" + email_id + '.xlsx')
        mime.add_header('Content-Disposition','attchment',filename=reply_path)
        mime.add_header('Content-ID','<0>')
        mime.add_header('X-Attachment-ID','0')
        mime.set_payload(f.read())
        encoders.encode_base64(mime)
        msg.attach(mime)

    #定义发送文件
    server=smtplib.SMTP_SSL(smtp_server,465)
    server.set_debuglevel(1)
    server.login(from_addr,password)
    server.sendmail(from_addr,to_addr,msg.as_string())

server.quit()